<?php
/**
 * Search Engine Main Page
 *
 * @author Xavier VILAIN
 *
 */

use Doctrine\DBAL\Types\Type;
use Fox\Common\Localization\SubRegion;
use Doctrine\DBAL\Connection;
use Fox\Common\Localization;
use Hive\Ressource\Wish;
use Fox\Query;
use Fox\Behaviors;
use Fox\Query\Messages;
use Doctrine\DBAL\Schema\Index;
use Hive\Ressource;

use Fox\Html\DropList\Confirm;
use Fox\Html\Button;
use Fox\Html\Interval;
use Fox\Html\Input;
use Fox\Html\Screen;
use Fox\Dao\SqlFragment;
use Fox\Generator;
use Fox\Html\DropList;
use Fox\Html\Anchor;
use Fox\SearchEngine;
// Init
$xview = Xview::instance();
$ajax = $xview->val("ajaxPost",false);
$forced = $xview->val("forced",false);
$missionid  = $xview->val("mid");
$queryFields = $xview->val("queryFields",false);
// Customizing
$customizing = Hive\Mission\Customizing::instance();
$customizing->trackSummaryPage(array('mod' => 'missions','act'=>'matches'));
$output = null;
// HTML Components
$screen = new Screen("sSearchRessource","vlook",false,true);
$screen->responseTarget('#searchResult');
$screen->action(Generator::url('missions','matches',null,array('mid' => $missionid)));
$screen->mainTitle(Msg(Hive::BUNDLE,Hive::TITLE_CONDIDAT_SEARCH));
//$screen->toolbar()->add(new Fox\Html\Button("add","?mod=ressource&act=add",Msg(Hive::BUNDLE,12)));
$wrapper = $xview->component('ressource', 'criteria');
$screen->add($wrapper);

if(!empty($queryFields)) {
	$data = Query::fieldsToArray($queryFields);
	$forced = true;
	$screen->initialize($data);
}
$screen->add(new Button('savequery','?',Msg(Messages::BUNDLE,Messages::BUT_SAVE_AS_QUERY)))
				->behaviors(Behaviors::action(
					Generator::url('queries','query',null),
					true,
					'#sSearchRessource'
				));
if(!$screen->item('bAdvanceCriteria')->initial()) {
	$screen->item('bAdvanceCriteria')->collapsed(false);
}
if($screen->submitted() || isset($_POST['keyword']) || $forced)
{
	$inputs = $screen->get();
	
	$data = json_decode(preg_replace("/\'/",'"',$inputs['localization']),true);
	if(is_array($data)) {
		$screen->item('criteria')->item('bAdvanceCriteria')->collapsed(false);
		foreach($data as $item) {
			$id = $item['id'];
			$idValue = substr($id,2);
			switch (substr($id,0,1)) {
				case "r" :
					$regions[] = $idValue;
					break;
				case "g" :
					$groups[] = $idValue;
					break;
				case "s" :
					$subs[] = $idValue;
					break;
			}
		}
		if(isset($groups)) {
			$regionsBuffer = Localization::loadRegionFromGroups($groups);
			$locWishesFragment[] = new SqlFragment("wl.level = ? AND wl.id IN (?)",array(1,$groups),
														array(Type::INTEGER,Connection::PARAM_INT_ARRAY));
			if(isset($regions)) {
				$regions = array_merge($regions,$regionsBuffer);
			} else {
				$regions = $regionsBuffer;
			}
		}
		
		// We need to add the sub filter
		if(isset($subs) || isset($regions)) {
			// Load States Id
			$query = Fox\Dao::infos(SubRegion::className())->handle()->createQueryBuilder();
			$query->select('stateid')->from('region_subs','t');
			if(isset($regions)) {
				$regFragment[] = new SqlFragment("regid IN (?)",array($regions),array(Connection::PARAM_INT_ARRAY));
				$locWishesFragment[] = new SqlFragment("wl.level = ? AND wl.id IN (?)",array(2,$regions),
														array(Type::INTEGER,Connection::PARAM_INT_ARRAY));
			} 
			if(isset($subs)) {
				$regFragment[] = new SqlFragment("regsubid IN (?)",array($subs),array(Connection::PARAM_INT_ARRAY));
				$locWishesFragment[] = new SqlFragment("wl.level = ? AND wl.id IN (?)",array(3,$subs),
														array(Type::INTEGER,Connection::PARAM_INT_ARRAY));
			}
			$regFragment = SqlFragment::combine($regFragment, SqlFragment::MODE_OR);
			$stateids = $regFragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_COLUMN);
			$locWishesFragment[] = new SqlFragment("a.stateid IN (?)",array($stateids),array(Connection::PARAM_INT_ARRAY));
		}
	}
	
	
	$interviewed = $wrapper->item("hasbeenintv")->toInt();
	if(!$screen->errors())
	{
		$joins = null;
		$jobInfo = false;

		$filters[] = $wrapper->item("kn_number_year")->fragments();
		
		if(!empty($inputs["status"]))
		{
			$filters[] = $wrapper->item("status")->fragments();
		}
		if(!$wrapper->item("kn_number_year")->initial()) {
			
		}
		$handler = Fox\Databases::databases()->handle(Fox\Databases::DB_APP);
		$query = $handler->createQueryBuilder();
		$query->select("DISTINCT t.ressource_id","t.status","t.last_name","t.first_name","t.email","t.kn_number_year",
						"a.phone1","a.phone2","a.city","a.postal_code","a.country",
						"exp.compid","exp.jobtitle","exp.salary","exp.bonus","c.company_name")
						  ->from("ressources","t")
						  ->leftJoin("t","address","a","t.adrid = a.adrid")
						  ->leftJoin("t","ressource_experiences","exp","t.ressource_id = exp.ressource_id AND current = 1")
						  ->leftJoin("t","companies","c","exp.compid = c.compid")
						  ->setMaxResults($inputs['dbcnt']);
			  
		// Manage Interview
		if($interviewed !== null) {
			if($interviewed) {
				$query->innerJoin("t","ressource_interviews","intv","t.ressource_id = intv.ressource_id");
			} else {
				$query->leftJoin("t","ressource_interviews","intv","t.ressource_id = intv.ressource_id");
				$filters[] = new Fox\Dao\SqlFragment("intv.iid IS NULL ");
			}
		}
		
		// Filter on Salary in wishes
		if(!$wrapper->item("targeted_total_salary")->initial())
		{
			$filters[] = $wrapper->item("targeted_total_salary")->fragments();
			$query->innerJoin("t","ressource_desires","des","des.ressource_id = t.ressource_id AND des.current = 1");
		}
		// Filter on whishes localizaton 
		if(isset($locWishesFragment)) {
			$locWishesFragment = SqlFragment::combine($locWishesFragment, SqlFragment::MODE_OR);
			if(!$wrapper->item("targeted_total_salary")->initial()) {
				$query->innerJoin("t","ressource_wish_localizations","wl","des.ressource_id = wl.ressource_id");
			} else {
				$query->leftJoin("t","ressource_desires","des","des.ressource_id = t.ressource_id AND des.current = 1")
						->leftJoin("t","ressource_wish_localizations","wl","des.ressource_id = wl.ressource_id");
			}
		}
		// Experience
		if(!$wrapper->item("available_date")->initial())
		{
			$jobInfo = true;
			$filters[] =  $wrapper->item("available_date")->fragments("end_date");
		} else {
			
		}
		
		// Mission Filter
		$mission = Xview::instance()->component("missions", "object");
		foreach($mission->candidates() as $candidate) {
			$candidatesId[] = $candidate->partnerid;
		}
		if(isset($candidatesId)) {
			$filters[] = new SqlFragment("t.ressource_id NOT IN (?)",array($candidatesId),array(Connection::PARAM_INT_ARRAY));
		}
		
		try {
			$result = array();
			if(empty($inputs['keyword']))
			{
				$finder = new Hive\Ressource();
				$filters[] = new SqlFragment("t.loekz = ?",array(0));
			//	if($localization)
			//		$joins[] = Hive\Ressource::$JOIN_DESIRE;
			//	$result = $finder->find($filters,array("last_name" => "ASC"),$joins,$inputs['dbcnt'],true);
				$fragment = SqlFragment::combine($filters);
				$handler->beginTransaction();
				$handler->prepare("SET SQL_BIG_SELECTS =1")->execute();
				$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_OBJ); 
				$handler->commit();
			}
			else
			{
				$searchEngine = new SearchEngine();
				$keywords = preg_replace("/\'/"," ",strtolower($inputs['keyword']));
				$matches = $searchEngine->search(explode(' ',$keywords),SearchEngine::MODE_LIKE);
				if(is_array($matches))
				{
					foreach($matches as $match)
					{
						$groupingIds[] = $match['grouping_id'];
					}
					$fragments = $filters;
					$fragments[] = isset($groupingIds) ?  new Fox\Dao\SqlFragment("t.ressource_id IN (?)",
															array($groupingIds),
															array(Doctrine\DBAL\Connection::PARAM_STR_ARRAY)) : null;
					// 
					$fragment = Fox\Dao\SqlFragment::combine($fragments);
					$handler->beginTransaction();
					$handler->prepare("SET SQL_BIG_SELECTS =1")->execute();
					$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_OBJ);
					$handler->commit();
					if(count($result) == 0)
							Fox\Message::Warning(Msg(Fox\Messages\SearchEngine::BUNDLE,Fox\Messages\SearchEngine::NO_DOCUMENT_FOUND));
				}
				else
				{
					Fox\Message::Warning(Msg(Fox\Messages\SearchEngine::BUNDLE,Fox\Messages\SearchEngine::NO_DOCUMENT_FOUND));
				}

			}
			//---- Output View	----
			/* @var $grid Fox\Grid */
			$grid = Xview::instance()->component("ressource","result");
			$grid->title(Msg(Fox::MSGCL,143,count($result)));
			$grid->fieldcat()->setGenerators(array(
				'salary' => Fox\Generator::useFunction(function ($data,$key){
					return $data->salary + $data->bonus;
				}),
				'dummy' => Fox\Generator::useFunction(function ($data,$key){
					$country = $data->country;
					if(empty($country) || $country == 'fr')
					{
						$postal_code = substr($data->postal_code,0,2);
						$adress = $data->address()->city;
						$adress .= empty($postal_code) ? null : ' ('.$postal_code.')';
					}
					else
					{
						$adress = \Fox\Ddic\DataElement\Text::label('country',$data->country);
					}
					return $adress;
			
				}),
				'experience' => Fox\Generator::useFunction(function ($data,$key){
					return "<b>{$data->jobtitle}</b> chez {$data->company_name}";
				}),
			));
			
			$grid->initialize($result);
			$grid->setXlsDownload();
			$grid->setColumnManager();
			$grid->layout()->scrollVertical(true);
			$grid->footer()->add(new Button('select',null,Msg(Hive::BUNDLE,Hive::BUT_SHOW)));
			$grid->footer()->add(new Button('assign',null,
					Msg(Hive::BUNDLE,Hive::BUT_ASSIGN_CONTACTS_TO_CURRENT_MISSION)))
						->behaviors(Behaviors::action(
							Generator::url("missions","ressource","assign",array('missionid' => $missionid)),
						false,null,null,true,'POST',"bd_".$grid->id(),null,null,true));
			$output = $grid->Display();
		}
		catch (Exception $e)
		{
			Fox\Message::Error($e->getMessage());
		}
	}

}
if(!$ajax)
{
?>
	<div id="matches">
	<div><?php echo $screen->render();?></div>
	<div class="matches-contentArea">
		<div class="mbs"  id="searchResult">
				<?php echo $output;?>
		</div>

	</div>
</div>
<?php 
}
else
{
	echo Xview::instance()->messages();
	echo $output;
}